﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Model;
using System.Data;
using System.Data.SqlClient;

namespace DAL
{
    public class PptQuestionEntityDAL
    {
        private SQLHelper sqlhelper = null;
        public  PptQuestionEntityDAL()
        {
           
            sqlhelper = new SQLHelper();
        }
        #region 加载试题信息到界面
        /// <summary>
        /// 加载试题信息到界面
        /// </summary>
        /// <param name="pptinfo">传递学生号</param>
        /// <returns>返回题目的内容</returns>
        public DataTable LoadPptByFlag(PptQuestionEntity pptinfo)
        {
            DataTable dt = new DataTable();
            string sql = "select QuestionID,QuestionFlag,QuestionContent,Fration, PaperType,RightAnswer from  PptQuestionEntity  where PaperType=@PaperType and QuestionFlag=@QuestionFlag";
            SqlParameter[] paras = new SqlParameter[]{
                new  SqlParameter("@PaperType",pptinfo.PaperType  ),
                new SqlParameter ("@QuestionFlag",pptinfo.QuestionFlag )
            };
            dt = sqlhelper.ExecuteQuery(sql, paras, CommandType.Text);
            return dt;
        }
        #endregion

        #region 根据试题的类型加载试题的信息--周洲--2015年11月21日
        /// <summary>
        /// 加载试题到界面中
        /// </summary>
        /// <param name="pptinfo">传递学生号</param>
        /// <returns>返回题目的内容</returns>
        public DataTable LoadPptQuestion(PptQuestionEntity pptinfo)
        {
            DataTable dt = new DataTable();
            string sql = "select * from PptQuestionEntity where paperType =@PaperType";
            SqlParameter[] paras = new SqlParameter[]{
                new  SqlParameter("@PaperType",pptinfo.PaperType )
            };
            dt = sqlhelper.ExecuteQuery(sql, paras, CommandType.Text);
            return dt;
        }
        #endregion

        #region 将答案和分数更新到数据库中-陈晓婵-2015年12月26日11:29:11
        /// <summary>
        /// 判分之后返回信息到数据库
        /// </summary>
        /// <param name="studentinfo">根据考生ID判断</param>
        /// <returns>是否添加成功</returns>
        
        public void ReturnScore(List<PptQuestionRecordEntity> list, PptQuestionRecordEntity studentrecord)      
        {
            String which = WhichPptRecored(studentrecord);
            StringBuilder sbSql = new StringBuilder();
            sbSql.Append("update PptQuestionRecordEntity_" + which + " SET Fration = CASE QuestionID ");
            string Fation = string.Empty;
            string questionID = string.Empty;
            string studentIDs = string.Empty;
            string tamstamp = string.Empty;

            //根据学号，和时间戳选出这一段的内容，然后再根据QuestionID给每一个flag更新分数。
            #region 更新分数的拼接sql语句
            for (int i = 0; i < list.Count; i++)
            {
                questionID = list[i].QuestionID.ToString();
                //将分数传给变量
                Fation = list[i].Fration;
                //判分sql，拼接sql，用case，when语句
                sbSql.Append(" WHEN '" + questionID + "' THEN '" + Fation + "'");
            }
            sbSql.Append(" end,"); //加“，” 
            #endregion

            #region 拼接考生答案更新到数据库中
            //拼接更新考生答案sql
            sbSql.Append("  ExamAnswer = CASE QuestionID");
            for (int i = 0; i < list.Count; i++)
            {
                string examAnswer = list[i].ExamAnswer.ToString();
                questionID = list[i].QuestionID.ToString();
                //学生答案信息更新，when后边的是QuerstionID,
                sbSql.Append(" WHEN '" + questionID + "' THEN '" + list[i].ExamAnswer + "'");
            }
            sbSql.Append(" end");   //不加逗号“，” 
            #endregion

           
                studentIDs += "'" + list[0].StudentID.ToString() + "'";
                tamstamp += "'" + list[0].TimeStamp.ToString() + "'";
            
            studentIDs.Remove(studentIDs.Length - 1, 1);
            sbSql.Append(" where StudentID in (" + studentIDs + ") and TimeStamp in (" + tamstamp + ")");
            DataTable dt = sqlhelper.ExecuteQuery(sbSql.ToString(), CommandType.Text);
        }

        #endregion

        #region 根据学号选择题型  陈晓婵
        /// <summary>
        /// 根据学号选择题型
        /// </summary>
        /// <param name="studentinfo">传递学生号</param>
        /// <returns>试卷类型</returns>
        public DataTable SelectPaperTypeByStudentID(StudentInfoEntity studentinfo)
        {
            DataTable dt = new DataTable();
            string sql = "select PaperType from PptQuestionRecordEntity   where StudentID = @studentinfo";
            SqlParameter[] paras = new SqlParameter[]{
                new  SqlParameter("@studentinfo",studentinfo.studentID )
            };
            dt = sqlhelper.ExecuteQuery(sql, paras, CommandType.Text);
            return dt;

        }
        #endregion

        #region 查找所有的Ppt的套卷 PptPaperType() 邱慕夏 2015年11月20日16:57:30
        /// <summary>
        /// 查找所有的Ppt的套卷
        /// </summary>
        /// <returns></returns>
        public DataTable PptPaperType()
        {
            DataTable dt = new DataTable();
            string sql = "select Distinct PaperType from PptQuestionEntity";
            dt = sqlhelper.ExecuteQuery(sql, CommandType.Text);
            return dt;
        }
        #endregion

        #region 给PptQutionEntity分组 邱慕夏 2015年11月20日16:57:30
        /// <summary>
        /// 查找所有的Ppt的套卷
        /// </summary>
        /// <returns></returns>
        public DataTable PptPaperTypeGroupByPaperType()
        {
            DataTable dt = new DataTable();
            string sql = "select * from PptQuestionEntity group by PaperType";
            dt = sqlhelper.ExecuteQuery(sql, CommandType.Text);
            return dt;
        }
        #endregion

        #region 根据PaperType查询分组 邱慕夏 2015年11月20日16:57:30
        /// <summary>
        ///给PptQutionEntity分组
        /// </summary>
        /// <returns></returns>
        public DataTable PptPaperTypeGroupByPaperType(String PaperType)
        {
            DataTable dt = new DataTable();
            string sql = "select * from PptQuestionEntity where PaperType=@PaperType";
            SqlParameter[] paras = new SqlParameter[]{
                new  SqlParameter("@PaperType",PaperType )
            };
            dt = sqlhelper.ExecuteQuery(sql, paras, CommandType.Text);
            return dt;
        }
        #endregion

        #region 根据学生的ID查询是否该学生是要往哪个表中进行insert--邱慕夏  2015年11月23日14:28:57
        /// <summary>
        /// 根据学生的ID查询是否该学生是要往哪个表中进行insert--邱慕夏
        /// </summary>
        /// <param name="studentinfo">根据CollegeID判断学生在那个表中</param>
        /// <returns>是否添加成功</returns>
        public int InsertPptRecord(PptQuestionRecordEntity studentrecord)
        {
            String which = WhichPptRecored(studentrecord);
            string sql = "Insert into PptQuestionRecordEntity_" + which + "(QuestionID,StudentID,PaperType,QuestionContent,RightAnswer) values(@QuestionID,@StudentID,@PaperType,@QuestionContent,@RightAnswer)";
            SqlParameter[] paras = new SqlParameter[]{
                new SqlParameter("@studentID",studentrecord.StudentID ),
                new SqlParameter ("@PaperType",studentrecord.PaperType ),
                new SqlParameter ("@QuestionContent",studentrecord.QuestionContent),
                new SqlParameter ("@RightAnswer",studentrecord.RightAnswer),
                new SqlParameter ("@QuestionID",studentrecord.QuestionID )
            };
            int flag = sqlhelper.ExecuteNonQuery(sql, paras, CommandType.Text);
            return flag;
        }
        #endregion

        #region 根据学生的ID查询去重--邱慕夏  2015年11月23日14:28:57
        /// <summary>
        /// 根据学生的ID查询去重--邱慕夏
        /// </summary>
        /// <param name="studentinfo">根据CollegeID判断学生在那个表中</param>
        /// <returns>是否已经添加</returns>
        public Boolean SelectPptRecord(PptQuestionRecordEntity studentrecord)
        {
            String which = WhichPptRecored(studentrecord);
            string sql = "Select * from  PptQuestionRecordEntity_" + which + " where StudentID=@studentID";
            SqlParameter[] paras = new SqlParameter[]{
                new SqlParameter("@studentID",studentrecord.StudentID ),
            };
            DataTable dt = sqlhelper.ExecuteQuery(sql, paras, CommandType.Text);
            if (dt.Rows.Count == 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }
        #endregion

        #region 批量根据学生的ID查询是否该学生是要往哪个表中进行insert--邱慕夏
        /// <summary>
        /// 批量根据学生的ID查询是否该学生是要往哪个表中进行insert--邱慕夏
        /// </summary>
        /// <param name="studentinfo">根据CollegeID判断学生在那个表中</param>
        /// <returns>是否添加成功</returns>
        public int InsertPptRecordList(List<PptQuestionRecordEntity> studentrecordlist, StudentInfoEntity student)
        {
            //int flag = 0;
            //for (int i = 0; i < studentrecordlist.Count; i++)
            //{
            //    String which = student.CollegeID.Trim();
            //    string sql = "Insert into PptQuestionRecordEntity_" + which + "(QuestionID,StudentID,PaperType,QuestionContent,RightAnswer) values(@QuestionID,@StudentID,@PaperType,@QuestionContent,@RightAnswer)";
            //    SqlParameter[] paras = new SqlParameter[]{
            //    new SqlParameter("@studentID",studentrecordlist[i].StudentID ),
            //    new SqlParameter ("@PaperType",studentrecordlist[i].PaperType ),
            //    new SqlParameter ("@QuestionContent",studentrecordlist[i].QuestionContent),
            //    new SqlParameter ("@RightAnswer",studentrecordlist[i].RightAnswer),
            //    new SqlParameter ("@QuestionID",studentrecordlist[i].QuestionID )
            //    };
            //    flag = sqlhelper.ExecuteNonQuery(sql, paras, CommandType.Text);
            //}


            int flag = 0;

            if (studentrecordlist.Count != 0)
            {
                String which = student.CollegeID;
                String sql = "Insert into PptQuestionRecordEntity_" + which + "(QuestionID,StudentID,PaperType,QuestionContent,RightAnswer,TimeStamp) ";
                for (int i = 0; i < studentrecordlist.Count; i++)
                {
                    if (i != studentrecordlist.Count - 1)
                    {
                        sql = sql + "select '" + studentrecordlist[i].QuestionID + "','" + studentrecordlist[i].StudentID + "','" + studentrecordlist[i].PaperType + "','" + studentrecordlist[i].QuestionContent + "','" + studentrecordlist[i].RightAnswer + "','" + studentrecordlist[i].TimeStamp + "' union all ";
                    }
                    else
                    {
                        sql = sql + "select '" + studentrecordlist[i].QuestionID + "','" + studentrecordlist[i].StudentID + "','" + studentrecordlist[i].PaperType + "','" + studentrecordlist[i].QuestionContent + "','" + studentrecordlist[i].RightAnswer + "','" + studentrecordlist[i].TimeStamp + "'; ";
                    }
                }
                flag = sqlhelper.ExecuteNonQuery(sql, CommandType.Text);
                return flag;
            }
            else
            {
                return 1;
            }

        }
        #endregion

        #region 看看向哪个数据库表中进行填写数据  邱慕夏  2015年11月23日14:26:41
        /// <summary>
        /// 看看向哪个数据库表中进行填写数据
        /// </summary>
        /// <param name="studentrecord"></param>
        /// <returns></returns>
        public String WhichPptRecored(PptQuestionRecordEntity studentrecord)
        {
            DataTable dt = new DataTable();
            string sql = "select collegeId  from StudentInfoEntity where studentID =@studentID";
            SqlParameter[] paras = new SqlParameter[]{
                new SqlParameter("@studentID",studentrecord.StudentID ),
            };
            dt = sqlhelper.ExecuteQuery(sql, paras, CommandType.Text);
            String which = dt.Rows[0][0].ToString();
            return which;
        }
        #endregion

        #region 查询该考生 是否有资格考试 -赵崇-2015年11月24日 16:39:57
        /// <summary>
        /// 查询该考生 是否有资格考试
        /// </summary>
        /// <param name="pEnStudent">学生</param>
        /// <returns>true表示可以进行考试，false表示 未进行配置 所以不能考试</returns>
        public bool GetIsCanExamByStudent(StudentInfoEntity pEnStudent)
        {

            string tableName = "";
            tableName = "PptQuestionRecordEntity_" + pEnStudent.CollegeID;

            //1,查询所有的选择题
            DataTable dt = new DataTable();
            string sql = "select * from " + tableName + " where StudentID=@StudentID";
            SqlParameter[] paras = new SqlParameter[]{
                new SqlParameter ("@StudentID",pEnStudent.studentID )
            };
            dt = sqlhelper.ExecuteQuery(sql, paras, CommandType.Text);

            if (dt.Rows.Count == 0)
            {
                return false;
            }
            return true;
        }
        #endregion


        #region 根据试卷类型查询试卷分数配置——陈新月——2018-04-16
        public DataTable InqueryPPTInfo(PptQuestionEntity pptQuestion)
        {
            DataTable dt;
            string sql = "select Fration as 分值,QuestionFlag as 题型标志 from PptQuestionEntity where PaperType=@PaperType";
            SqlParameter[] paras = new SqlParameter[] {
                new SqlParameter("@PaperType",pptQuestion.PaperType)
            };
            dt = sqlhelper.ExecuteQuery(sql, paras, CommandType.Text);
            return dt;
        }
        #endregion

        #region 根据试卷类型更新试卷分数配置——陈新月——2018-04-17
        public int UpdatePPTInfo(List<PptQuestionEntity> listPPT)
        {
            int UpdatePPT = 0;
            for (int i = 0; i < listPPT.Count; i++)
            {
                string sql = "update PPTQuestionEntity set Fration=@Fration,QuestionFlag=@QuestionFlag where PaperType=@PaperType and QuestionFlag=@QuestionFlag";
                SqlParameter[] paras = new SqlParameter[] {
                new SqlParameter("@PaperType",listPPT[i].PaperType),
                new SqlParameter("@Fration",listPPT[i].Fration),
                new SqlParameter("@QuestionFlag",listPPT[i].QuestionFlag)
                };
                UpdatePPT = sqlhelper.ExecuteNonQuery(sql, paras, CommandType.Text);

            }
            return UpdatePPT;
        }
        #endregion

        #region 配置PPT题-吕德彪-2018——陈新月——2018-5-5 17:19:02
        /// <summary>
        /// 配置PPT题
        /// </summary>
        /// <returns></returns>
        public int AddPptQuestion(DataTable dt)
        {
            //查询数据库里有多少条数据
            PptQuestionEntityDAL pptentity = new PptQuestionEntityDAL();
            DataTable table = pptentity.SelectQuestionID();
            int sum = table.Rows.Count;

            string sql = "";
            int flag = 0;
            int i = 0;
            int j = 0;
            SqlParameter[] paras = null;
            for ( i = 0; i < dt.Rows.Count; i++)
            {
                sql = "insert into PptQuestionEntity values(@QuestionID,@QuestionTypeID,@PaperType,@Fration,@QuestionFlag,@QuestionContent,@CorrectAnswer,@PlaceFlag)";
                paras = new SqlParameter[] {
                    new SqlParameter("@QuestionID", (i + 1 + sum).ToString()),
                    new SqlParameter("@QuestionTypeID", dt.Rows[i][1].ToString()),
                    new SqlParameter("@PaperType", dt.Rows[i][2].ToString()),
                    new SqlParameter("@Fration", dt.Rows[i][3].ToString()),
                    new SqlParameter("@QuestionFlag", dt.Rows[i][4].ToString()),
                    new SqlParameter("@QuestionContent", dt.Rows[i][5].ToString()),
                    new SqlParameter("@CorrectAnswer", dt.Rows[i][6].ToString()),
                    new SqlParameter ("@PlaceFlag",dt.Rows[i][7].ToString ())
                    };
                try
                {                   
                    //捕获错误数据的标志
                    flag = sqlhelper.ExecuteNonQuery(sql, paras, CommandType.Text);
                    j++;
                }
                catch (Exception)
                {
                    //返回错误数据的标志
                    return j;
                }
                
            }
            return j;
        }
        #endregion

        #region 查询pptQuestionEntity表中有多少条数据-吕德彪-2018
        /// <summary>
        /// 查询PptQuestionEntity表中有多少条数据
        /// </summary>
        /// <returns></returns>
        public DataTable SelectQuestionID()
        {
            string sql = "select QuestionID from PptQuestionEntity";
            SqlParameter[] paras = new SqlParameter[] { };
            DataTable table = sqlhelper.ExecuteQuery(sql, paras, CommandType.Text);
            return table;
        }
        #endregion

    }
}
